﻿using System.Data;
using System.Data.SQLite;
using System.Text.RegularExpressions;

namespace test1
{
    public class DbHelp
    {
        private SQLiteConnection db = new SQLiteConnection("Data Source=gbd.db");

        public DbHelp()
        {
            db.Open();
            this.execute(@"CREATE TABLE IF NOT EXISTS st13(
                st13 VARCHAR(50) PRIMARY KEY,
                txt1 TEXT NOT NULL,
                txt2 TEXT NOT NULL
            )");

            this.execute(@"CREATE TABLE IF NOT EXISTS brands711_2(
                st13 VARCHAR(50) PRIMARY KEY,
                logo TEXT,
                brandName TEXT,
                status VARCHAR(20),
                markFeature TEXT,
                office TEXT,
                type TEXT,
                applicationNumber TEXT,
                applicationDate VARCHAR(10),
                registrationNumber TEXT,
                registrationDate VARCHAR(10),
                expiryDate VARCHAR(10),
                kind TEXT,
                niceClass TEXT,
                applicantCountryCode TEXT,
                classificationCode TEXT,
                applicantsName TEXT,
                applicantsAddr TEXT,
                applicantsCountry TEXT,
                publicationDate VARCHAR(10),
                filingPlace TEXT,
                applicationLanguageCode TEXT,
                markVerbalElement TEXT,
                representativesKind TEXT,
                representativesName TEXT,
                representativesAddr TEXT,
                representativesCountry TEXT
            )");

        }

        ~DbHelp() { db.Close(); }

        private static Regex regexParams = new Regex(@"@(\w+)");
        public SQLiteDataReader select(string sql, params dynamic[] pms)
        {
            var cmd = db.CreateCommand();
            cmd.CommandText = sql;
            int ix = 0;
            foreach (var item in regexParams.Matches(sql).Cast<Match>())
            {
                cmd.Parameters.AddWithValue(item.Groups[1].Value, pms[ix++]);
            }

            return cmd.ExecuteReader();
        }

        public dynamic[]? selectRow(string sql, params dynamic[] pms)
        {
            var read = select(sql, pms);
            if (read.Read())
            {
                object[] objects = new object[read.FieldCount];
                read.GetValues(objects);
                return objects;
            }
            return null;
        }

        public T? selectObjct<T>(string sql, params dynamic[] pms) where T : new()
        {

            var tp = typeof(T);
            var read = select(sql, pms);
            if (read.Read())
            {
                var obj = new T();
                foreach (var p in tp.GetProperties())
                {
                    try
                    {
                        string? val = read.GetFieldValue<String?>(p.Name);

                        p.SetValue(obj, val);
                    }
                    catch (Exception ex) { 
                        Console.WriteLine(ex.ToString());
                    }
                }
                return obj;
            }
            return default(T);
        }

        public List<T> selectObjects<T>(string sql, params dynamic[] pms) where T : new()
        {
            var list = new List<T>();
            var tp = typeof(T);
            var read = select(sql, pms);
            while (read.Read())
            {
                var obj = new T();
                foreach (var p in tp.GetProperties())
                {
                    string val = read.GetFieldValue<String>(p.Name);
                    p.SetValue(obj, val);
                }
                list.Add(obj);
            }

            return list;
        }

        public dynamic? selectOne(string sql, params dynamic[] pms)
        {
            var read = select(sql, pms);
            if (read.Read())
            {
                return read.GetValue(0);
            }
            return null;
        }

        public int execute(string sql, params dynamic[] pms)
        {
            var cmd = db.CreateCommand();
            cmd.CommandText = sql;
            int ix = 0;
            foreach (var item in regexParams.Matches(sql).Cast<Match>())
            {
                cmd.Parameters.AddWithValue(item.Groups[1].Value, pms[ix++]);
            }
            return cmd.ExecuteNonQuery();
        }

        public int execute(string sql, Object obj)
        {
            var cmd = db.CreateCommand();
            cmd.CommandText = sql;
            var tp = obj.GetType();
            foreach (var item in regexParams.Matches(sql).Cast<Match>())
            {
                var key = item.Groups[1].Value;
                var p = tp.GetProperty(key);
                cmd.Parameters.AddWithValue(key, p.GetValue(obj));
            }
            return cmd.ExecuteNonQuery();
        }


    }
}
